﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VR.Model;
using HCQ2_Model.VR.Params;
using HCQ2_Model.VRDD.InputDto._210908;
using HCQ2_Model.VRDD.InputDto._211217;
using HCQ2_Model.VRDD.OutputDto._211217;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDSpecMainDAL: IT_VRDDSpecMainDAL
    {
        /// <summary>
        ///  获取规格列表
        /// </summary>
        /// <param name="isMoney"></param>
        /// <param name="isType"></param>
        /// <param name="specName"></param>
        /// <returns></returns>
        public List<SpecResultList> GetSpecResultList(int fileId, int isMoney, string isType, string specName)
        {
            sb?.Clear();
            sb.Append($@"SELECT specID,specName,specMoney,specType,
                specOptionName=(SELECT STUFF(
	                (SELECT ','+optionName FROM dbo.T_VRDDSpecChild
	                WHERE specID=m.specID FOR XML PATH('')),1,1,''
                ) FROM dbo.T_VRDDSpecChild a WHERE specID=m.specID GROUP BY a.specID)
                ,specOptionPic=(SELECT STUFF(
	                (SELECT ','+optionImage FROM dbo.T_VRDDSpecChild
	                WHERE specID=m.specID FOR XML PATH('')),1,1,''
                ) FROM dbo.T_VRDDSpecChild a WHERE specID=m.specID GROUP BY a.specID)
                ,specOptionId=(SELECT STUFF(
	                (SELECT ','+CAST(optionID AS NVARCHAR(100))  FROM dbo.T_VRDDSpecChild
	                WHERE specID=m.specID FOR XML PATH('')),1,1,''
                ) FROM dbo.T_VRDDSpecChild a WHERE specID=m.specID GROUP BY a.specID)
                FROM T_VRDDSpecMain m WHERE fileId={fileId} ");
            if (!string.IsNullOrEmpty(specName))
                sb.Append($" AND specName LIKE '%{specName}%' ");
            if (!string.IsNullOrEmpty(isType))
                sb.Append($" AND specType='{isType}' ");
            if (isMoney > -1)
                sb.Append($" AND specMoney={isMoney} ");
            return SqlHelper.ExecuteList<SpecResultList>(sb.ToString());
        }
        /// <summary>
        ///  删除已不在的子规格数据
        /// </summary>
        /// <param name="optionId"></param>
        /// <param name="specId"></param>
        /// <returns></returns>
        public int DeleteSpecChild(List<string> optionId, int specId)
        {
            string option = string.Join(",", optionId);
            sb?.Clear();
            sb.Append($@"DELETE FROM dbo.T_VRDDSpecChild WHERE specID={specId} AND optionID NOT IN({option});");
            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }
        /// <summary>
        ///  获取待删除子规格图片附件地址
        /// </summary>
        /// <param name="optionId"></param>
        /// <param name="specId"></param>
        /// <returns></returns>
        public List<string> GetDeleteSpecChildPic(List<string> optionId, int specId)
        {
            string option = string.Join(",", optionId);
            sb?.Clear();
            sb.Append($@"SELECT optionImage FROM dbo.T_VRDDSpecChild WHERE specID={specId} AND optionID NOT IN({option}) AND ISNULL(optionImage,'')<>'';");
            return SqlHelper.ExecuteList<string>(sb.ToString());
        }
        /// <summary>
        ///  获取重置库存、价格数据信息
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public List<VRSpecChildOption> GetSpecChildOption(int fileId)
        {
            sb?.Clear();
            sb.Append($@"SELECT m.*,c.optionID FROM 
            (SELECT specID,specMoney,fileId FROM dbo.T_VRDDSpecMain WHERE fileId={fileId})m INNER JOIN
            (SELECT specID,optionID FROM dbo.T_VRDDSpecChild WHERE fileId={fileId})c ON c.specID = m.specID;");
            return SqlHelper.ExecuteList<VRSpecChildOption>(sb.ToString());
        }
        /// <summary>
        ///  获取商品库存列表
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public List<SpecRepertoryModel> GetSpecRepertoryList(int fileId)
        {
            sb?.Clear();
            sb.Append($@"SELECT keyCode,repName=(SELECT stuff((select ',' + (SELECT specName FROM 
            dbo.T_VRDDSpecMain WHERE specID=g.specID)+':'+(SELECT optionName FROM dbo.T_VRDDSpecChild
            WHERE optionID=g.optionID) FROM T_VRDDSpecGroup g WHERE groupCode = a.groupCode AND 
            fileId={fileId} ORDER BY g.specID,g.optionID for xml path('')), 1, 1, '') FROM T_VRDDSpecGroup a 
            WHERE a.groupCode=s.groupCode AND a.fileId={fileId} GROUP BY a.groupCode) 
            ,quantity FROM dbo.T_VRDDSpecRepertory s WHERE s.fileId={fileId};");
            return SqlHelper.ExecuteList<SpecRepertoryModel>(sb.ToString());
        }
        /// <summary>
        ///  获取商品价格列表
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public List<SpecPriceModel> GetSpecPriceList(int fileId)
        {
            sb?.Clear();
            sb.Append($@"SELECT s.keyCode,priceName=(SELECT stuff((select ',' + (SELECT specName FROM dbo.T_VRDDSpecMain 
            WHERE specID=g.specID)+':'+(SELECT optionName FROM dbo.T_VRDDSpecChild WHERE optionID=g.optionID)
            FROM T_VRDDSpecGroupPrice g WHERE groupCode = a.groupCode AND fileId={fileId} 
            ORDER BY g.specID,g.optionID for xml path('')), 1, 1, '') FROM T_VRDDSpecGroupPrice a 
            WHERE a.groupCode=s.groupCode AND a.fileId={fileId} GROUP BY a.groupCode) 
            ,s.specMoney FROM dbo.T_VRDDSpecPrice s WHERE s.fileId={fileId};");
            return SqlHelper.ExecuteList<SpecPriceModel>(sb.ToString());
        }
        /// <summary>
        ///  获取规格组合价格列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<GroupCodeModel> GetCommPriceList(CommPriceInput param)
        {
            sb?.Clear();
            sb.Append($@"SELECT a0.groupCode FROM 
            (SELECT groupCode FROM dbo.T_VRDDSpecGroupPrice WHERE
                fileId = {param.fileID} ");
            if (param.specOptionList!=null && param.specOptionList.Count > 0)
                sb.Append($" AND optionID = {param.specOptionList[0].optionID} ");
            sb.Append(") a0 ");
            if (param.specOptionList != null && param.specOptionList.Count > 1)
            {
                for (int i = 1; i < param.specOptionList.Count; i++)
                {
                    sb.Append($@" INNER JOIN (SELECT groupCode FROM dbo.T_VRDDSpecGroupPrice WHERE 
                fileId={param.fileID} AND optionID={param.specOptionList[i].optionID}) a{i} ON a{i}.groupCode = a{i - 1}.groupCode ");
                }
            }
            return SqlHelper.ExecuteList<GroupCodeModel>(sb.ToString());
        }
        /// <summary>
        ///  获取规格组合价格
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public decimal GetCommPrice(int fileId,string groupCode)
        {
            sb?.Clear();
            sb.Append($"SELECT TOP 1 specMoney FROM dbo.T_VRDDSpecPrice WHERE fileId={fileId} AND groupCode='{groupCode}';");
            object result = SqlHelper.ExecuteScalar(sb.ToString());
            if (result == null)
                return (decimal)0;
            return HCQ2_Common.Helper.ToDecimal(result);
        }
        /// <summary>
        ///  获取规格组合库存列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<GroupCodeModel> GetCommStockList(CommPriceInput param)
        {
            sb?.Clear();
            sb.Append($@"SELECT a0.groupCode FROM 
            (SELECT groupCode FROM dbo.T_VRDDSpecGroup WHERE
                fileId = {param.fileID} AND optionID = {param.specOptionList[0].optionID}) a0 ");
            if (param.specOptionList.Count > 1)
            {
                for (int i = 1; i < param.specOptionList.Count; i++)
                {
                    sb.Append($@" INNER JOIN (SELECT groupCode FROM dbo.T_VRDDSpecGroup WHERE 
                fileId={param.fileID} AND optionID={param.specOptionList[i].optionID}) a{i} ON a{i}.groupCode = a{i - 1}.groupCode ");
                }
            }
            return SqlHelper.ExecuteList<GroupCodeModel>(sb.ToString());
        }
        /// <summary>
        ///  获取规格组合库存
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCommStock(int fileId, string groupCode)
        {
            sb?.Clear();
            sb.Append($"SELECT TOP 1 quantity FROM dbo.T_VRDDSpecRepertory WHERE fileId={fileId} AND groupCode='{groupCode}';");
            object result = SqlHelper.ExecuteScalar(sb.ToString());
            if (result == null)
                return 0;
            return HCQ2_Common.Helper.ToInt(result);
        }
        /// <summary>
        ///  app获取规格列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputDDSpecList> GetDDSpecList(InputDDSpecParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT specID,specName,specMoney FROM dbo.T_VRDDSpecMain 
            WHERE fileId={param.fileID} AND specType='{param.specType}' ");
            if (param.specMoney > -1)
                sb.Append($" AND specMoney={param.specMoney} ");
            return SqlHelper.ExecuteList<OutputDDSpecList>(sb.ToString());
        }
    }
}